﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class ProjectApprove_BLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProjectByYear(int Year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@Year", Year);
                string sql = "select  CONCAT(e.first_name,' ',e.last_name ) full_name, r.bgm_req_seq as Sequence, r.bgm_req_code as bgm_prj_code , r.bgm_req_name as bgm_prj_name, s.bgm_strat_list_name as Strategy, r.record_status "
                           + ", s.bgm_strat_list_name , j.job_site_name, e.first_name, e.last_name"
                           + ", (select coalesce(sum(total_amount), 0)"
                           + " from bgm_request_expense e where e.bgm_req_seq = r.bgm_req_seq) as total_amount"
                           + " from bgm_request r"
                           + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                           + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                           + " left join hrm_ps_employees e on r.bgm_req_manager = e.emp_seq"
                           + " where r.bgm_year = @Year and r.record_statusnot like@RecordStatus";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequenceForGridIn(string seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", seq);
                string sql = "select s.bgm_source_name, coalesce(sum(ae.total_amount), 0) as total_amount "
                           + " from  bgm_request r"
                           + " left join bgm_request_expense ae on ae.bgm_req_seq = r.bgm_req_seq"
                           + " left join bgm_mas_source s on ae.bgm_source_seq = s.bgm_source_seq"
                           + " where r.bgm_req_seq = @seq"
                           + " group by s.bgm_source_seq, s.bgm_source_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void InsertProjectStatus(string bgm_req_seq, string sequence_no, string emp_seq, string pos_seq, string comment)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@sequence_no", sequence_no);
                dbManager.AddParameters(2, "@emp_seq", emp_seq);
                dbManager.AddParameters(3, "@pos_seq", pos_seq);
                dbManager.AddParameters(4, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(5, "@comment", comment);
                string strsql = "Insert into bgm_request_status ("
                                            + "bgm_req_seq,"
                                            + "sequence_no,"
                                            + "emp_seq,"
                                            + "pos_seq,"
                                            + "record_status,"
                                            + "comment)"
                                            + " values("
                                            + "@bgm_req_seq,"
                                            + "@sequence_no,"
                                            + "@emp_seq,"
                                            + "@pos_seq,"
                                            + "@record_status,"
                                            + "@comment)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                strsql = "update bgm_request set record_status = 'A' where bgm_req_seq = @bgm_req_seq";  // test fix P --> A
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.CommitTransaction();
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
